<?php

namespace app\admin\service\work_area;

use app\common\model\broadcast\DeviceRod;
use app\common\model\work_area\HkDeviceSerial;
use app\common\model\work_area\ProjLine;
use app\common\model\work_area\WorkArea;
use app\common\model\work_area\WorkAreaStand;
use app\common\model\work_area\WorkAreaUser;
use app\common\service\haikang\HaikangService;
use app\common\service\tencent\VoiceService;
use think\db\Query;

/**
 * 工区相关服务
 */
class BusinessService
{

    //当前数据库只记录了下面事件
    public static $event_arr = [
        'ai_message_task_result'
    ];

    /**
     * 获取项目列表
     * @param $param
     * @return array
     * @throws \think\db\exception\DbException
     */
    public static function projLineList($param): array
    {
        $page_size = empty($param['page_size']) ? 10 : $param['page_size'];
        //条件
        $where = [];
        //模糊查询
        if (!empty($param['keyword'])) {
            $where[] = ['name', 'like', "%{$param['keyword']}%"];
        }
        //时间查询
        if (!empty($param['keyword_time'])) {
            $time = 'create_time';//查询时间字段
            $timeWhere = timeWhere($param['keyword_time'][0], $param['keyword_time'][1]);
            $where[] = [$time, '>=', $timeWhere[0]];
            $where[] = [$time, '<=', $timeWhere[1] + 86399];
        }
        //排序
        $order = ['id' => 'DESC'];
        if (!empty($param['order']) && !empty($param['order_name'])) {
            $order_name = $param['order_name'];
            switch ($param['order']) {
                case 1:
                    $order = [$order_name => 'DESC'];
                    break;
                case 2:
                    $order = [$order_name => 'ASC'];
                    break;
            }
        }
        $list_ = ProjLine::where($where)
            ->field([
                'id', 'name', 'sort', 'remarks', 'create_time', 'line_style', 'line_arr',
            ])
            ->order($order)
            ->paginate($page_size)
            ->toArray();
        $list = $list_ ['data'];
        return [
            'list' => $list,
            'total' => $list_['total'],
            'page_size' => $page_size
        ];
    }

    /**
     * 获取项目详情
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function projLineInfo($param): array
    {
        $info = ProjLine::where(['id' => $param['id']])
            // ->cache('ProjLine',600)
            ->field('id,name,sort,remarks,line_arr,line_style')
            ->find()
            ->toArray();
        $info['line_arr'] = $info['line_arr'] ?? [];
        return $info;
    }

    /**
     * 项目增加、修改
     * @param $param
     * @param $admin_id
     * @return void
     */
    public static function projLineSave($param, $admin_id)
    {
        $data = [
            'admin_id' => $admin_id,
            'name' => trim($param['name']),
            'sort' => trim($param['sort']),
            'remarks' => trim($param['remarks']),
            'line_arr' => $param['line_arr'] ?? null,
            'line_style' => $param['line_style'] ?? null,
        ];
        $info = new ProjLine();
        //修改
        if (isset($param['id'])) {
            $data['id'] = $param['id'];
            $info->cache('ProjLine')->update($data);
        } else {
            //添加
            $info->save($data);
        }
    }

    /**
     * 获取工区人员列表
     * @param $param
     * @return array
     */
    public static function workAreaUserList($param): array
    {
        $page_size = empty($param['page_size']) ? 10 : $param['page_size'];
        //条件
        $where = [];
        //模糊查询
        if (!empty($param['keyword'])) {
            $where[] = ['a.account|a.remarks|b.name', 'like', "%{$param['keyword']}%"];
        }
        //时间查询
        if (!empty($param['keyword_time'])) {
            $time = 'a.create_time';//查询时间字段
            $timeWhere = timeWhere($param['keyword_time'][0], $param['keyword_time'][1]);
            $where[] = [$time, '>=', $timeWhere[0]];
            $where[] = [$time, '<=', $timeWhere[1] + 86399];
        }
        //排序
        $order = ['a.create_time' => 'DESC'];
        if (!empty($param['order']) && !empty($param['order_name'])) {
            $order_name = $param['order_name'];
            switch ($param['order']) {
                case 1:
                    $order = [$order_name => 'DESC'];
                    break;
                case 2:
                    $order = [$order_name => 'ASC'];
                    break;
            }
        }
        $list_ = WorkAreaUser::alias("a")
            ->join("work_area b", " b.id = a.work_area_id", 'left')
            ->where($where)
            ->field([
                'a.id', 'a.account', 'a.type', 'a.remarks', 'a.create_time',
                'b.name',
            ])
            ->group('a.id')
            ->order($order)
            ->paginate($page_size)
            ->toArray();
        $list = $list_ ['data'];
        if (!empty($list)) {
            foreach ($list as $k => $v) {
                $list[$k]['type'] = WorkAreaUser::$typev[$v['type']];
            }
        }
        $work_area_list = WorkArea:: field('id,remarks name')->select();
        $work_area_stand = WorkAreaStand:: field('id,name')->select();
        return [
            'list' => $list,
            'total' => $list_['total'],
            'page_size' => $page_size,
            'work_area_list' => $work_area_list,
            'work_area_stand' => $work_area_stand,
        ];
    }

    /**
     * 获取人员详情
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function workAreaUserInfo($param)
    {
        $info = WorkAreaUser::where(['id' => $param['id']])
            // ->cache('WorkAreaUser',600)
            ->field('id,account,type,work_area_id,work_area_stand_id,remarks,account_name,account_logo')
            ->find()
            ->toArray();
        $info['password'] = '';
        $info['password_confirm'] = '';
        return $info;
    }

    /**
     * 人员添加修改
     * @param $param
     * @param $admin_id
     * @return void
     * @throws \Exception
     */
    public static function workAreaUserSave($param, $admin_id)
    {
        $info = (new WorkAreaUser());
        $password = md5(md5(trim($param['password'])) . "admin");
        if ($param['type'] == 2 && empty($param['work_area_id'])) throw new \Exception('请选择工区');
        if ($param['type'] == 3 && empty($param['work_area_stand_id'])) throw new \Exception('请选择站点');
        $data = [
            'admin_id' => $admin_id,
            'account' => trim($param['account']),
            'type' => $param['type'],
            'work_area_id' => $param['work_area_id'],
            'work_area_stand_id' => $param['work_area_stand_id'],
            'remarks' => $param['remarks'],
            'account_name' => $param['account_name'],
            'account_logo' => $param['account_logo'],
            'password' => $password
        ];
        //修改
        if (isset($param['id'])) {
            $data['id'] = $param['id'];
            if (empty($param['password'])) unset($data['password']);
            $repeat = WorkAreaUser::where([
                ['id', '<>', $data['id']],
                ['account', '=', $data['account']],
            ])->value('id');
            if (!empty($repeat)) throw new \Exception("账号:{$data['account']}已存在");
            $info->cache('WorkAreaUser')->update($data);
        } else {
            $repeat = WorkAreaUser::where('account', $data['account'])->value('id');
            if (!empty($repeat)) throw new \Exception("账号:{$data['account']}已存在");
            //添加
            $info->save($data);
        }
    }

    /**
     * 工区列表
     * @param $param
     * @return array
     * @throws \think\db\exception\DbException
     */
    public static function WorkAreaList($param)
    {
        $page_size = empty($param['page_size']) ? 10 : $param['page_size'];
        //条件
        $where = [];
        //模糊查询
        if (!empty($param['keyword'])) {
            $where[] = ['a.name|a.addr|b.name', 'like', "%{$param['keyword']}%"];
        }
        //时间查询
        if (!empty($param['keyword_time'])) {
            $time = 'a.create_time';//查询时间字段
            $timeWhere = timeWhere($param['keyword_time'][0], $param['keyword_time'][1]);
            $where[] = [$time, '>=', $timeWhere[0]];
            $where[] = [$time, '<=', $timeWhere[1] + 86399];
        }
        //排序
        $order = ['a.id' => 'DESC'];
        if (!empty($param['order']) && !empty($param['order_name'])) {
            $order_name = $param['order_name'];
            switch ($param['order']) {
                case 1:
                    $order = [$order_name => 'DESC'];
                    break;
                case 2:
                    $order = [$order_name => 'ASC'];
                    break;
            }
        }
        $list_ = WorkArea::alias("a")
            ->join("proj_line b", " b.id = a.proj_line_id", 'left')
            ->where($where)
            ->field([
                'a.id', 'a.name', 'a.addr', 'a.sort', 'a.remarks', 'a.create_time', 'a.is_use', 'a.abbr',
                'b.name bname'
            ])
            ->order($order)
            ->paginate($page_size)
            ->toArray();
        $list = $list_ ['data'];
        if (!empty($list)) {
            foreach ($list as $k => $v) {
                $list [$k]['is_use'] = WorkAreaStand::$is_use_v[$v['is_use']];
            }
        }
        //项目线列表
        $proj_line_list = ProjLine::field(['id', 'name'])->select()->toArray();

        return [
            'list' => $list,
            'total' => $list_['total'],
            'page_size' => $page_size,
            'proj_line_list' => $proj_line_list,
        ];
    }

    /**
     * 工区添加、修改
     * @param $param
     * @param $admin_id
     * @return void
     */
    public static function WorkAreaSave($param, $admin_id)
    {
        $info = (new WorkArea());
        $data = [
            'admin_id' => $admin_id,
            'proj_line_id' => $param['proj_line_id'],
            'name' => trim($param['name']),
            'addr' => trim($param['addr']) ?? '',
            'sort' => $param['sort'],
            'is_use' => $param['is_use'],
            'remarks' => $param['remarks']
        ];
        //修改
        if (isset($param['id'])) {
            $data['id'] = $param['id'];
            $info->cache('WorkArea')->update($data);
        } else {
            $info->save($data);
        }
    }


    /**
     * 获取工区详情
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function workAreaInfo($param)
    {
        $info = WorkArea::where(['id' => $param['id']])
            ->field('id,proj_line_id,name,addr,longitude,latitude,sort,remarks,is_use,abbr')
            ->find()
            ->toArray();
        return $info;
    }


    /**
     * 工区下站列表
     * @param $param
     * @return array
     * @throws \think\db\exception\DbException
     */
    public static function WorkAreaStandList($param)
    {
        $page_size = empty($param['page_size']) ? 10 : $param['page_size'];
        //条件
        $where = [];
        //模糊查询
        if (!empty($param['keyword'])) {
            $where[] = ['a.name|b.name', 'like', "%{$param['keyword']}%"];
        }
        //时间查询
        if (!empty($param['keyword_time'])) {
            $time = 'a.create_time';//查询时间字段
            $timeWhere = timeWhere($param['keyword_time'][0], $param['keyword_time'][1]);
            $where[] = [$time, '>=', $timeWhere[0]];
            $where[] = [$time, '<=', $timeWhere[1] + 86399];
        }
        //排序
        $order = ['a.id' => 'DESC'];
        if (!empty($param['order']) && !empty($param['order_name'])) {
            $order_name = $param['order_name'];
            switch ($param['order']) {
                case 1:
                    $order = [$order_name => 'DESC'];
                    break;
                case 2:
                    $order = [$order_name => 'ASC'];
                    break;
            }
        }
        $list_ = WorkAreaStand::alias("a")
            ->join("work_area b", " b.id = a.work_area_id", 'left')
            ->where($where)
            ->field([
                'a.*',
                'b.name bname'
            ])
            ->order($order)
            ->paginate($page_size)
            ->toArray();
        $list = $list_ ['data'];
        if (!empty($list)) {
            foreach ($list as $k => $v) {
                $list [$k]['is_use'] = WorkAreaStand::$is_use_v[$v['is_use']];
                $list [$k]['yield_time'] = date('Y-m-d', $v['yield_time']);
            }
        }
        $work_area_list = WorkArea::field('id,remarks name,proj_line_id')->with(['projLineInfo' => function (Query $query) {
            $query->field('id,line_arr,line_style,name');
        }])->select();

        return [
            'list' => $list,
            'total' => $list_['total'],
            'page_size' => $page_size,
            'work_area_list' => $work_area_list
        ];
    }

    /**
     * 工区下站点添加、修改
     * @param $param
     * @param $admin_id
     * @return void
     */
    public static function WorkAreaStandSave($param, $admin_id)
    {
        $info = (new WorkAreaStand());
        $param['admin_id'] = $admin_id;
        $param['yield_time'] = strtotime($param['yield_time']);
        unset($param['create_time']);
        //修改
        if (isset($param['id'])) {
            $info->update($param);
        } else {
            $info->save($param);
        }
    }

    /**
     * 获取工区下站详情
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function WorkAreaStandInfo($param)
    {
        $info = WorkAreaStand::where(['id' => $param['id']])
            ->field('*')
            ->find()
            ->toArray();
        $info['yield_time'] = empty($info['yield_time']) ? date('Y-m-d') : date('Y-m-d', $info['yield_time']);
        return $info;
    }

    /**
     * 获取摄像头录像机列表
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function deviceSerialList($param)
    {
        $page_size = empty($param['page_size']) ? 10 : $param['page_size'];
        //条件
        $where = [];
        //模糊查询
        if (!empty($param['keyword'])) {
            $where[] = ['a.name|a.device_serial|b.name', 'like', "%{$param['keyword']}%"];
        }
        //时间查询
        if (!empty($param['keyword_time'])) {
            $time = 'a.create_time';//查询时间字段
            $timeWhere = timeWhere($param['keyword_time'][0], $param['keyword_time'][1]);
            $where[] = [$time, '>=', $timeWhere[0]];
            $where[] = [$time, '<=', $timeWhere[1] + 86399];
        }
        //排序
        $order = ['a.id' => 'DESC'];
        if (!empty($param['order']) && !empty($param['order_name'])) {
            $order_name = $param['order_name'];
            switch ($param['order']) {
                case 1:
                    $order = [$order_name => 'DESC'];
                    break;
                case 2:
                    $order = [$order_name => 'ASC'];
                    break;
            }
        }
        $list_ = HkDeviceSerial::alias("a")
            ->join("work_area_stand b", " b.id = a.work_area_stand_id", 'left')
            ->where($where)
            ->field([
                'a.id', 'a.name', 'a.comply_with', 'a.device_serial', 'a.lefts,a.tops', 'a.type', 'a.channel_no', 'a.sort', 'a.remarks', 'a.yj_name', 'a.create_time',
                'b.name bname'
            ])
            ->order($order)
            ->paginate($page_size)
            ->toArray();
        $list = $list_ ['data'];
        if (!empty($list)) {
            foreach ($list as $k => $v) {
                $list[$k]['type'] = HkDeviceSerial::$typev[$v['type']];
            }
        }
        $work_area_stand_list = WorkAreaStand::field('id,name,work_area_id')->select();
        $device_rod = DeviceRod::field('id,name')->select();
        return [
            'list' => $list,
            'total' => $list_['total'],
            'page_size' => $page_size,
            'work_area_stand' => $work_area_stand_list,
            'device_rod' => $device_rod
        ];
    }


    /**
     * 获取摄像头录像机详情
     * @param $param
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function deviceSerialInfo($param)
    {
        $info = HkDeviceSerial::where(['id' => $param['id']])
            ->field('*')
            ->find()
            ->toArray();
        return $info;
    }


    /**
     * 摄像头添加、修改
     * @param $param
     * @param $admin_id
     * @return void
     */
    public static function deviceSerialSave($param, $admin_id)
    {
        $info = (new HkDeviceSerial());
        $data = [
            'device_rod_id' => $param['device_rod_id'],
            'admin_id' => $admin_id,
            'name' => trim($param['name']),
            'comply_with' => trim($param['comply_with']),
            'device_serial' => trim($param['device_serial']),
            'work_area_stand_id' => $param['work_area_stand_id'],
            'channel_no' => $param['channel_no'],
            'type' => $param['type'],
            'rtp_type' => $param['rtp_type'],
            'lefts' => $param['lefts'],
            'tops' => $param['tops'],
            'sort' => $param['sort'],
            'yj_name' => $param['yj_name'],
            'remarks' => $param['remarks'],
            'rtp_device_serial' => $param['device_serial'],
            'rtp_channel_no' => $param['channel_no'],
            'rtp_url' => $param['rtp_url'],
            'rtp_url_whole' => $param['rtp_url_whole'] ?? '',
        ];
        //修改
        if (isset($param['id'])) {
            $hk_device_serial = HkDeviceSerial::where([
                'device_serial' => $param['device_serial'],
                'channel_no' => $param['channel_no']
            ])->where('id', '<>', $param['id'])->value('id');
            if (!empty($hk_device_serial)) throw new \Exception("已存在设备号{$param['device_serial']}通道号{$param['channel_no']}");
            $data['id'] = $param['id'];
            $info->update($data);
        } else {
            $hk_device_serial = HkDeviceSerial::where([
                'device_serial' => $param['device_serial'],
                'channel_no' => $param['channel_no']
            ])->value('id');
            if (!empty($hk_device_serial)) throw new \Exception("已存在设备号{$param['device_serial']}通道号{$param['channel_no']}");
            $info->save($data);
            $data['id'] = $info->id;
        }
        
        if (!empty($param['device_serial'])) {
            //添加音频文件
            
            VoiceService::workAreaStand($data['id']);
        }

    }

    /**
     * 通过项目id获取所有摄像头设备号
     * @param $id
     * @return mixed
     */
    public static function projDeviceList($id)
    {
        $work_area_id = WorkArea::where(['proj_line_id' => $id])->column('id');
        $device_serial = HkDeviceSerial::where('work_area_stand_id', 'IN', function ($query) use ($work_area_id) {
            $query->name('work_area_stand')->where('work_area_id', 'in', $work_area_id)->where('delete_time', 0)->field('id');
        })
            ->group("device_serial")
            ->column('device_serial', 'id');
        return $device_serial;
    }

    /**
     * 通过工区id获取全部录像机
     * @param $id
     * @return array
     */
    public static function areaDeviceList($id)
    {
        $device_serial = HkDeviceSerial::where('work_area_stand_id', 'IN', function ($query) use ($id) {
            $query->name('work_area_stand')->where('work_area_id', 'in', $id)->where('delete_time', 0)->field('id');
        })
            ->group("device_serial")
            ->column('device_serial', 'id');
        return $device_serial;
    }


    /**
     * 通过站点id获取全部录像机
     * @param $id
     * @return array
     */
    public static function siteDeviceList($id)
    {
        $device_serial = HkDeviceSerial::where('work_area_stand_id', '=', $id)
            ->group("device_serial")
            ->column('device_serial', 'id');
        return $device_serial;
    }

    /**
     * 工区相关导出
     * @param $expCellName 数据key字段
     * @param $data 导出数据数组
     * @param $type 1指定工区导出 2全部工区统计
     * @return void
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function workAreaExport($expCellName, $data, $type = 1)
    {
        ini_set('memory_limit', '1024M');
        $expTableData = $data['list'];
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $cellName = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S',
            'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL',
            'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        );
        $template = 'xlsx/站点表格导出模板.xlsx';
        if ($type == 2) $template = 'xlsx/工区导出统计模板.xlsx';


        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template);
        $sheet = $spreadsheet->getActiveSheet();

        if ($type == 1) {
            $sheet->setCellValue('A1', $data['a1']);
            $sheet->setCellValue('B3', $data['b3']);
            $sheet->setCellValue('C3', $data['c3']);
            $sheet->setCellValue('D3', $data['d3']);
            $sheet->setCellValue('E3', $data['e3']);
            $sheet->setCellValue('F3', $data['f3']);
            $sheet->setCellValue('G3', $data['g3']);
            $sheet->setCellValue('H3', $data['h3']);
            /** 开始从数据库提取信息插入Excel表中 */
            for ($i = 7; $i < $dataNum + 7; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    if (isset($expTableData[$i - 7][$expCellName[$j][0]])) {
                        $sheet->setCellValue($cellName[$j] . $i, $expTableData[$i - 7][$expCellName[$j][0]]);
                    }
                }
            }
        } else {
            /** 开始从数据库提取信息插入Excel表中 */
            for ($i = 3; $i < $dataNum + 3; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    if (isset($expTableData[$i - 3][$expCellName[$j][0]])) {
                        $sheet->setCellValue($cellName[$j] . $i, $expTableData[$i - 3][$expCellName[$j][0]]);
                    }
                }
            }
        }

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $time = date('mdHis');
        $file = "xlsx/{$time}count.xlsx";
        $writer->save($file);
        return $file;
    }


}